<?php
require_once __DIR__.'/PHPExcel.php';
/**
 * phpexcel处理类
 * @author Toby.tu 2016-07-20
 */
class ExcelLib {
	private $document_path;
	private $filePath=false;
	private $PHPExcel;
	private $objWriter;
	public $tem_file;
	public $current_sheet;
	public function __construct(){
		$this->document_path=($_SERVER['DOCUMENT_ROOT']);
	}

     public function toExcel($titleArray,$datas,$fileName=false,$sheetIndex=0,$sheetTitle=false,$subPath=false,$append=false,$heading=''){
        if(!$this->filePath){
	     	if(!$fileName){
	     		$fileName=ic_micro_time().".xls";
	     	}
	     	$this->openExcel($fileName,$subPath);
        }
		//设置当前的sheet
		try {
			$objActSheet = $this->PHPExcel->getSheet($sheetIndex);
		}catch (\Exception $e){
			$ss=$this->PHPExcel->createSheet(null);
			$objActSheet=$ss;
		}
		//设置sheet的name
		if(!$sheetTitle){
			$objActSheet->setTitle ( $sheetIndex."" );
		}
		else {
			//$sheetTitle=$this->formatSheetTitle($sheetTitle,$sheetIndex."");
			try{
				//dd( $sheetTitle);
				$objActSheet->setTitle ( $sheetTitle );
			}catch (\Exception $e){
				//$objActSheet->setTitle ( $sheetIndex."" );
				//throw new  Exception("Error Processing Request", 1);
				throw $e;
			}
		}
		//填充数据
		$initCol="A";
		$col=$initCol;
		$colWidth=array();
		if($append){
			$row=$objActSheet->getHighestRow();
		}
		else{
			$row=1;
		}

		if(!$append){
			if(!empty($heading)){
				$title_count = count($titleArray);
				$title_center = floor($title_count / 2);//获取中间值
				for($i=0;$i<$title_count;++ $i){
				    $excel_col = ' ';$len = 0;
					if($i == $title_center){
						$excel_col = $heading;
						$len = $this->ic_strlen_2in1($excel_col);
					}
					//$objActSheet->getStyle($col.$row)->getAlignment()->setWrapText(true);
					$objActSheet->setCellValue ( $col.$row, $excel_col );
					$col++;
				}
				$col = $initCol;
				$row ++;
			}
			foreach ($titleArray as $title){
				$len=$this->ic_strlen_2in1($title);
				if(!array_key_exists($col,$colWidth)){
					$colWidth["{$col}"]=$len;
				}
				else if( $len>$colWidth["{$col}"]){
					$colWidth["{$col}"]=$len;
				}
				//$objActSheet->getStyle($col.$row)->getAlignment()->setWrapText(true);
				$objActSheet->setCellValue ( $col.$row, $title );
				$col++;
			}
		}
		$col=$initCol;
		$row++;
		if(!empty($datas)){
			foreach ($datas as $value){
				foreach ($value as $cell){
					//$len=$this->ic_strlen_2in1($cell);
				// if($len>$colWidth[$col]){
				// 	$colWidth[$col]=$len;
				// }
				//$objActSheet->getStyle($col.$row)->getAlignment()->setWrapText(true);
					//如果值以=开始，会被phpexcel认为是公式，所以这里在=前面加一个恶犬交空格
					if(!empty($cell)){
						if ($cell{0} === '=' && strlen($cell) > 1){
							$cell="　".$cell;
						}
					}else{
						$cell = '';
					}

					$objActSheet->setCellValue ( $col.$row, $cell );
					//$objActSheet->getColumnDimension($col.$row)->setAutoSize(true);
					$col++;
				}
				$col=$initCol;
				$row++;
			}
		}

		//控制每列宽度A4B2C4D5E5F4G4H5I4J4K5L4
		foreach ($colWidth as $key=> $val){
			if($val>25){
				$val=25;
			}
			$objActSheet->getColumnDimension($key)->setWidth($val*3);
		}
		//$this->saveExcel();
		//需合并单元格设计
		//$objActSheet->mergeCells('A1:H2');
		//设置宽度
		//$objActSheet->getColumnDimension('A')->setWidth(20);
		//颜色设计
//		$objActSheet->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
//		$objActSheet->getStyle('A1')->getFill()->getStartColor()->setARGB('EEEE00');
		// 字体 设计
//		$objActSheet->getStyle('A1')->getFont()->setName('Candara');
//		$objActSheet->getStyle('A1')->getFont()->setSize(20);
		return $this->current_sheet = $objActSheet;

     }

     private function openExcel($fileName,$subPath=false){
		$this->filePath=  $this->document_path.$subPath.$fileName; // 设置附件上传目录
		ini_set('memory_limit','2000M');
		set_time_limit(3600);
		if(!file_exists($this->filePath)){
			$this->createExcel();
		}
		else {
		$PHPReader = new \PHPExcel_Reader_Excel5();
		if(!$PHPReader->canRead($this->filePath)){
//			$PHPReader = new PHPExcel_Reader_Excel5();
//			if(!$PHPReader->canRead($filePath)){
//					return null ;
//			}
             throw new \Exception("无法读取{$this->filePath}文件,请检查是否为Excel5格式",-1);
		}
		else {
			$this->PHPExcel = $PHPReader->load($this->filePath);
			//dump("read :".$filePath);
			$this->objWriter = new \PHPExcel_Writer_Excel5 ( $this->PHPExcel );
		}
		}
	}

	private function createExcel(){
		$this->PHPExcel = new \PHPExcel ();
		//保存excel—5格式
		$this->objWriter = new \PHPExcel_Writer_Excel5 ( $this->PHPExcel );
		//$this->PHPExcel = new PHPExcel_Writer_Excel5 ( $PHPExcel );

	}

	public function saveExcel(){
		//$objWriter= new PHPExcel_Writer_Excel5 ( $this->PHPExcel );
		if($this->objWriter){
			$this->objWriter->save($this->filePath);
			$this->filePath = false;
		}

	}
	public function downExcel(){
		if($this->objWriter){
    		$this->objWriter->save('php://output');
			$this->filePath = false;
		}
	}

	private function formatSheetTitle($title,$defaultValue){
			$re=(preg_replace("/[^\x{4e00}-\x{9fa5}]+$/u","", $title));
			if(!empty($re)){
				return $re;
			}
			return $defaultValue;
	}

	/**
 * @author  perry
 * @version 1.0 2015/8/24
 * @name  计算字符串长度，１个中文算１个，2个英文字符算１个
 *
 * @param unknown_type $str
 * @return unknown
 */
private function ic_strlen_2in1($info){
	$infoLen=$this->ic_strlen_utf8($info);
	//dump($infoLen);
	$strlen=strlen($info);
	//dump($strlen);
	$clen=($strlen-$infoLen)/2; //中文字符个数
	//dump($clen);
	$relLen=$clen+ceil(($infoLen-$clen)/2);
	return $relLen;
}

/**
 * @author  perry
 * @version 1.0 2015/8/24
 * @name  计算字符串长度，１个中文算１个，1个英文字符算１个
 *
 * @param unknown_type $str
 * @return unknown
 */
function ic_strlen_utf8($str) {
	$i = 0;
	$count = 0;
	$len = strlen ($str);
	while ($i < $len) {
		$chr = ord ($str[$i]);
		$count++;
		$i++;
		if($i >= $len) break;
		if($chr & 0x80) {
			$chr <<= 1;
			while ($chr & 0x80) {
				$i++;
				$chr <<= 1;
			}
		}
	}
	return $count;
}
	/**
	 * 读取xls文件
	 * @author Tujt 2015-09-17
	 */
	public function readExcel($fileName, $subPath, $sheet=0) {
	    $this->openExcel($fileName,$subPath);
		$PHPReader = new \PHPExcel_Reader_Excel2007($this->PHPExcel);        //建立reader对象
		$public_path = public_path();
		$filePath = $public_path.$subPath.'/'.$fileName;
		if(!$PHPReader->canRead($filePath)){
			$PHPReader = new \PHPExcel_Reader_Excel5();
			if(!$PHPReader->canRead($filePath)){
				return false;//no Excel
			}
        }
        $PHPExcel = $PHPReader->load($filePath);        //建立excel对象
		try{
			$currentSheet = $PHPExcel->getSheet($sheet);        //**读取excel文件中的指定工作表*/
		}catch (\Exception $e) {
            return array(); //没有第二张工作表，返回error
        }
		$sheetTitle = $currentSheet->getTitle();//获取工作表名称
        $allColumn = $currentSheet->getHighestColumn();        //**取得最大的列号*/
        $allRow = $currentSheet->getHighestRow();        //**取得一共有多少行*/
        $data = array();
        for($rowIndex=1;$rowIndex<=$allRow;$rowIndex++){        //循环读取每个单元格的内容。注意行从1开始，列从A开始
			for($colIndex='A';$colIndex<=$allColumn;$colIndex++){
				$addr = $colIndex.$rowIndex;
				$cell = $currentSheet->getCell($addr)->getValue();
				if($cell instanceof PHPExcel_RichText){ //富文本转换字符串
						$cell = $cell->__toString();
				}
				$data[$rowIndex][$colIndex] = $cell;
			}
        }
        return array('title'=>$sheetTitle,'data'=>$data);
	}
	/**
	 * 读取远程excel文件的数据
	 * @author Tujt 2015-11-27
	 */
	public function readHttpExcel($fileName, $subPath, $sheet=0) {
	    //$temfile_name = 'hour_integ_'.md5(time()).'.xls';//时间戳并加密，保证文件唯一，不会被其他进程访问
	    $public_path = public_path();
		$tempath = '/upload/reportexcel/reporttmp';
		$file = 'tem_file_'.md5(time().rand(10,99)).'.xls';
		\App\Libraries\Report\ReportExcel::createdir($public_path.$tempath);
		\App\Libraries\Report\ReportExcel::createfile($public_path.$tempath.'/'.$file);
		$fp_output = fopen($public_path.$tempath.'/'.$file, 'w');
		$this->tem_file[$file] = 1;
		$ch = @curl_init($subPath.'/'.$fileName);
		@curl_setopt($ch, CURLOPT_FILE, $fp_output);
		@curl_exec($ch);
		@curl_close($ch);
		return $this->readExcel($file, $tempath, $sheet);
	}
	/**
	 * 删除xls临时文件
	 * @author Tujt 2015-11-26
	 */
	public function clearTemFiles() {
		if(empty($this->tem_file)) return 0;
	    $tem_dir = public_path('upload/reportexcel/reporttmp');
		chmod($tem_dir, 0777);
		$folder = @opendir($tem_dir);
		if ($folder == false) return 0;
		$count = 0;
		$isfile = array('.'=>'1','..'=>'1','index.html'=>'1','index.htm'=>'1');
		while(false !== ($file = readdir($folder))){
			if(isset($isfile[$file])) continue;//不可删除
			if(is_file($tem_dir.'/'.$file) && isset($this->tem_file[$file])){
				if(substr($file,0,9) != 'tem_file_') continue;
				chmod($tem_dir.'/'.$file, 0777);
				if (@unlink($tem_dir.'/'.$file)) ++$count;
			}
		}
		closedir($folder);unset($this->tem_file);
		return $count;
	}

	/* 设置sheet 样式 */
	// 粗体
	public function setBold($code,$val=true){
		if(empty($this->current_sheet)) return false;
		if(is_array($code)){
			foreach($code as $c){
				$this->current_sheet->getStyle($c)->getFont()->setBold($val);//加粗
			}
		}else{
			$this->current_sheet->getStyle($code)->getFont()->setBold($val);//加粗
		}
	}
	// 居中
	public function setContent($code){
		if(empty($this->current_sheet)) return false;
		$h_c = \PHPExcel_Style_Alignment::HORIZONTAL_CENTER;
		$v_c = \PHPExcel_Style_Alignment::VERTICAL_CENTER;
		if(is_array($code)){
			foreach($code as $c){
				$this->current_sheet->getStyle($c)->getAlignment()->setHorizontal($h_c);
  				$this->current_sheet->getStyle($c)->getAlignment()->setVertical($v_c);
			}
		}else{
			$this->current_sheet->getStyle($code)->getAlignment()->setHorizontal($h_c);
  			$this->current_sheet->getStyle($code)->getAlignment()->setVertical($v_c);
		}
	}
	// 长度
	public function setWidth($code,$val){
		if(empty($this->current_sheet)) return false;
		if(is_array($code)){
			foreach($code as $c){
				$this->current_sheet->getColumnDimension($c)->setWidth($val);//加粗
			}
		}else{
			$this->current_sheet->getColumnDimension($code)->setWidth($val);//加粗
		}
	}
	// 颜色
	public function setColor($code,$val){
		if(empty($this->current_sheet)) return false;
		$f_s = \PHPExcel_Style_Fill::FILL_SOLID;
		if(is_array($code)){
			foreach($code as $c){
				$this->current_sheet->getStyle($c)->getFill()->setFillType($f_s);//加粗
				$this->current_sheet->getStyle($c)->getFill()->getStartColor()->setARGB($val);
			}
		}else{
			$this->current_sheet->getStyle($code)->getFill()->setFillType($f_s);//加粗
			$this->current_sheet->getStyle($code)->getFill()->getStartColor()->setARGB($val);
		}
	}
	public function setStyle($code,$style){
		if(empty($this->current_sheet)) return false;
		if(is_array($code)){
			foreach($code as $c){
				$this->current_sheet->getStyle($c)->applyFromArray($style);//加粗
			}
		}else{
			$this->current_sheet->getStyle($code)->applyFromArray($style);//加粗
		}
	}
	public function setBorder($x='',$y='',$no_set=array()){
		if(empty($x) || empty($y)) return false;
		$x_arr = explode(':',$x);
		$y_arr = explode(':',$y);
		$styleArray = array(
	        'borders' => array(
	            'allborders' => array(
	                'style' => \PHPExcel_Style_Border::BORDER_THIN,//细边框
	            ),
	        ),
	    );
	    //设置所有的边框样式
		for($i=$y_arr[0];$i<=$y_arr[1];$i++){
			$this->setStyle($x_arr[0].$i.':'.$x_arr[1].$i,$styleArray);
		}
		if(!empty($no_set)){
			$no_set = array_flip($no_set);
		}
		if(!isset($no_set['top'])){
			//设置外边框样式
			$styleArray = array(
		        'borders' => array(
		            'top' => array(
		                'style' => \PHPExcel_Style_Border::BORDER_THICK,//边框是粗的
		            ),
		        ),
		    );
		    $this->setStyle($x_arr[0].$y_arr[0].':'.$x_arr[1].$y_arr[0],$styleArray);//上边框
		}
		if(!isset($no_set['bottom'])){
			//设置外边框样式
			$styleArray = array(
		        'borders' => array(
		            'bottom' => array(
		                'style' => \PHPExcel_Style_Border::BORDER_THICK,//边框是粗的
		            ),
		        ),
		    );
		    $this->setStyle($x_arr[0].$y_arr[1].':'.$x_arr[1].$y_arr[1],$styleArray);//下边框
		}
	    if(!isset($no_set['right'])){
	    	//设置外边框样式
			$styleArray = array(
		        'borders' => array(
		            'right' => array(
		                'style' => \PHPExcel_Style_Border::BORDER_THICK,//边框是粗的
		            ),
		        ),
		    );
		    $this->setStyle($x_arr[1].$y_arr[0].':'.$x_arr[1].$y_arr[1],$styleArray);//右
	    }
	    if(!isset($no_set['left'])){
	    	//设置外边框样式
			$styleArray = array(
		        'borders' => array(
		            'left' => array(
		                'style' => \PHPExcel_Style_Border::BORDER_THICK,//边框是粗的
		            ),
		        ),
		    );
		    $this->setStyle($x_arr[0].$y_arr[0].':'.$x_arr[0].$y_arr[1],$styleArray);//左
	    }
	}
	function setContents($x='',$y=''){
		if(empty($x) || empty($y)) return false;
		$x_arr = explode(':',$x);
		$y_arr = explode(':',$y);
		$h_c = \PHPExcel_Style_Alignment::HORIZONTAL_CENTER;
		$v_c = \PHPExcel_Style_Alignment::VERTICAL_CENTER;
		//设置所有的边框样式
		for($i=$y_arr[0];$i<=$y_arr[1];$i++){
			$this->current_sheet->getStyle($x_arr[0].$i.':'.$x_arr[1].$i)->getAlignment()->setHorizontal($h_c);
			$this->current_sheet->getStyle($x_arr[0].$i.':'.$x_arr[1].$i)->getAlignment()->setVertical($v_c);
		}
	}
	function setColors($x='',$y='',$color){
		if(empty($x) || empty($y)) return false;
		$x_arr = explode(':',$x);
		if(count($x_arr) == 1){
			$x_arr[1] = $x_arr[0];
		}
		$y_arr = explode(':',$y);
		$f_s = \PHPExcel_Style_Fill::FILL_SOLID;
		//设置所有的边框样式
		for($i=$y_arr[0];$i<=$y_arr[1];$i++){
			$this->current_sheet->getStyle($x_arr[0].$i.':'.$x_arr[1].$i)->getFill()->setFillType($f_s);//加粗
			$this->current_sheet->getStyle($x_arr[0].$i.':'.$x_arr[1].$i)->getFill()->getStartColor()->setARGB($color);
		}
	}
}
